"""
Case Type   : fullsql
Case Name   : 校验full sql关键字段不为负数
Create At   : 2022/01/27
Owner       : zhao-pengcheng-g
Description :
    1.设置level级别并查看,创建unlogged表
    2.查询系统视图,关键字段为0的数据是否为空
    3.环境清理
Expect      :
    1.设置成功,创建表成功
    2.成功
    3.环境清理成功
History     :
"""

import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0006 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 'table_fullsql_0006'

    def test_fullsql(self):
        text = '---step1:设置level级别并查看,创建unlogged表,' \
               'expect:设置成功，创建表成功---'
        self.logger.info(text)
        create_cmd = f'''select reset_unique_sql('global', 'all', 1);
            set track_stmt_stat_level='l1,l1';
            show track_stmt_stat_level;
            set current_schema to public;
            drop table if exists {self.table_name};
            create unlogged table {self.table_name}
            (
            i_item_sk integer not null,
            i_item_id char(16) not null,
            i_rec_start_date date,
            i_rec_end_date date,
            i_item_desc varchar(200),
            i_current_price decimal(7,2),
            i_wholesale_cost decimal(7,2),
            i_brand_id integer,
            i_brand char(50),
            i_class_id integer,
            i_class char(50),
            i_category_id integer,
            i_category char(50),
            i_manufact_id integer,
            i_manufact char(50),
            i_size char(20),
            i_formulation char(20),
            i_color char(20),
            i_units char(10),
            i_container char(10),
            i_manager_id integer,
            i_product_name char(50)
            );'''
        create_msg = self.sh_primy.execut_db_sql(create_cmd)
        self.logger.info(create_msg)
        set_assert = create_msg.count(self.constant.SET_SUCCESS_MSG) == 2
        self.assertTrue(set_assert, '执行失败:' + text)
        self.assertIn('l1,l1' and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS,
                      create_msg, '执行失败:' + text)

        text = '---step2:查询系统视图,关键字段为0的数据是否为空,expect:成功:---'
        self.logger.info(text)
        select_cmd = f'''select count(*) > 0 
            from 
                dbe_perf.get_global_full_sql_by_timestamp\
                (now()-interval'10 min',now()) 
            where unique_query_id= (select unique_sql_id 
                from
                    dbe_perf.statement 
                    where query like '%{self.table_name}%' 
                        and (n_hard_parse<0 or n_returned_rows<0 
                            or n_tuples_fetched<0 or n_tuples_returned<0 
                            or n_tuples_inserted<0 or n_tuples_updated<0 
                            or n_tuples_deleted<0 or n_blocks_fetched<0 
                            or n_blocks_hit <0 or n_soft_parse <0));'''
        select_msg = self.sh_primy.execut_db_sql(select_cmd,
                                                 dbname='postgres')
        self.logger.info(select_msg)
        self.common.equal_sql_mdg(select_msg,
                                  '?column?', 'f', '(1 row)', flag='1', )

    def tearDown(self):
        text = '---step3: 环境清理,expect:成功:---'
        self.logger.info(text)
        drop_cmd = f'drop table {self.table_name};'
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_msg, '执行失败:' + text)
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0006 finish---')
